Show the code
Plot.dot(filteredData,
{
x: "date",
y: "measure",
stroke: "measure",
tip: true
})
.plot()version: 0.3.2
We are trying to improve our charting work with Observable JS.
Update Note: We have fixed the x-axis labels by properly converting to and dealing with date objects.
This page displays the measurements taken from our personal weather station. The raw measurements are taken every 15 minutes but we have aggregated the information when pulling it from our PostgreSQL database to every hour, day, and month.
Much improvement is required to make meaningful conclusions from the data and for them to make sense in the context of what the measurements mean.
Find the source code on GitHub at Kester Weather Visualization Site
# This function builds the PostgreSQL query required to aggregate the timeseries
# data to a specified level.
agg_query <- function(agg_function, type, agg_level){
if(agg_level == 'As Measured'){
query <- sprintf("SELECT time AS date,
type,
\"measurementValue\" AS measure,
\"measurementValue\" AS measure_min,
\"measurementValue\" AS measure_max
FROM sensor_data
WHERE type = '%s'",
type)
}else{
query <- sprintf("SELECT date,
type,
%s(\"measurementValue\") AS measure,
min(\"measurementValue\") AS measure_min,
max(\"measurementValue\") AS measure_max
FROM (
SELECT date_trunc('%s',time) AS date,
type,
\"measurementValue\"
FROM sensor_data
WHERE type = '%s') AS A
GROUP BY date,
type",
agg_function,
agg_level,
type)
}
return(query)
}
measure_gather <- function(con, measure_spec, measure_tib, agg_levels){
for(measure in 1:nrow(measure_spec)){
for(agg in agg_levels){
if(agg == 'As Measured'){
temp <- DBI::dbGetQuery(conn = con,
statement = agg_query(agg_function = measure_spec[measure,1],
type = measure_spec[measure,2],
agg_level = agg))
measure_tib <- dplyr::bind_rows(measure_tib,
tibble::tibble(aggregate_level = agg,
temp))
}else{
temp <- DBI::dbGetQuery(conn = con,
statement = agg_query(agg_function = measure_spec[measure,1],
type = measure_spec[measure,2],
agg_level = agg))
measure_tib <- dplyr::bind_rows(measure_tib,
tibble::tibble(aggregate_level = agg,
temp))
}
}
}
return(measure_tib)
}if(any(c(!local,update))){
load(file = "./connectionInfo.RData")
measure_spec <- tibble::tibble(fun = c('avg',
'avg',
'avg',
'avg',
'sum',
'avg',
'avg',
'avg'),
type = c('Air Humidity',
'Air Temperature',
'Barometric Pressure',
'Light Intensity',
'Rain Gauge',
'UV Index',
'Wind Direction Sensor',
'Wind Speed'))
agg_levels <- c('As Measured','month','day','hour')
measure_tib <- tibble::tibble(aggregate_level = "NA",
date = Sys.time(),
type = "NA",
measure = 1.1,
measure_min = 1.1,
measure_max = 1.1)[-1,]
con <- DBI::dbConnect(drv = RPostgreSQL::PostgreSQL(),
dbname = db,
host = host,
port = port,
user = user,
password = password)
measure_tib <- measure_gather(con = con,
measure_spec = measure_spec,
measure_tib = measure_tib,
agg_levels = agg_levels)
DBI::dbDisconnect(conn = con)
save(measure_tib,
file = "./weatherData.RDS")
rm(con,db,host,password,port,user)
}
if(all(local,file.exists("./weatherData.RDS"))){
load(file = "./weatherData.RDS")
}
if(exists(x = "measure_tib")){
ojs_define(measures = measure_tib)
}import { aq, op } from '@uwdata/arquero'
d3 = require("d3@7")
parser = d3.timeParse("%Y-%m-%d %H:%M:%S");measures_trans = aq.from(transpose(measures)).derive({ date: aq.escape(d => parser(d.date)) })
filteredData = measures_trans
.params({
m: measure_type,
t: time_aggregation
})
.filter((d,p) => op.includes(d.type, p.m) && op.includes(d.aggregate_level, p.t))